﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class DeliveryNoteItemDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DeliveryNoteItemDAL()
        {
        
        }

        public IList<DeliveryNoteItemInfo> GetDeliveryNoteItemWithParent(int dn_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<DeliveryNoteItemInfo> infoList = new List<DeliveryNoteItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@dn_seq", dn_seq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT dn.ICM_DN_ITEM_SEQ"
                            + ", v.ICM_VAT_SEQ"
                            + ", CAST(CONCAT(v.ICM_VAT_NAME, ' อัตราภาษี ', b.VAT_PERCENT , ' %') AS CHAR) AS ICM_VAT_NAME"
                            + ", b.VAT_PERCENT"
                            + ", COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME) AS ITEM_NAME"
                            + ", dn.ITEM_DESCR"
                            + ", dn.ITEM_QTY"
                            + ", pi.ITEM_QTY AS PO_ITEM_QTY"
                            + ", pi.NET_AMOUNT AS PO_NET_AMOUNT"
                            + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS UNIT_NAME"
                            + ", dn.ITEM_PRICE_EXCLUDE_VAT"
                            + ", dn.ITEM_PRICE_INCLUDE_VAT"
                            + ", dn.EXCLUDE_VAT_AMOUNT"
                            + ", dn.VAT_AMOUNT"
                            + ", dn.NET_AMOUNT"
                        + " FROM ICM_DELIVERY_NOTE_ITEM dn"
                            + " LEFT JOIN ICM_PURCHASE_ORDER_INSTALLMENT_ITEM pi ON dn.ICM_PO_INSTALL_ITEM_SEQ = pi.ICM_PO_INSTALL_ITEM_SEQ"
                            + " LEFT JOIN ICM_BIDDER_QUOTATION q ON pi.ICM_BIDDER_QUO_SEQ = q.ICM_BIDDER_QUO_SEQ"
                            + " LEFT JOIN ICM_BIDDER b ON q.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ "
                            + " LEFT JOIN ICM_PURCHASE_REQUEST_ITEM ri ON q.ICM_PR_ITEM_SEQ = ri.ICM_PR_ITEM_SEQ "
                            + " LEFT JOIN BGM_MAS_EXPENSE x ON ri.BGM_EXP_SEQ = x.BGM_EXP_SEQ "
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON ri.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ "
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ "
                            + " INNER JOIN ICM_SYS_VAT v ON b.ICM_VAT_SEQ = v.ICM_VAT_SEQ "
                        + " WHERE dn.ICM_DN_SEQ = @dn_seq"
                            + " AND q.SELECTED = @yes_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        DeliveryNoteItemInfo info = new DeliveryNoteItemInfo();
                        PurchaseOrderInstallmentItemInfo infoPoInstallItem = new PurchaseOrderInstallmentItemInfo();
                        BidderQuotationInfo infoBidderQuotation = new BidderQuotationInfo();
                        PurchaseRequestItemInfo infoPrItem = new PurchaseRequestItemInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        SysVatInfo infoVat = new SysVatInfo();
                        BidderInfo infoBidder = new BidderInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_DN_ITEM_SEQ"]);
                        infoVat.Seq = Convert.ToInt32(dr[i]["ICM_VAT_SEQ"]);
                        infoVat.Name = dr[i]["ICM_VAT_NAME"].ToString();
                        infoBidder.VatPercent = (Convert.IsDBNull(dr[i]["VAT_PERCENT"]) ? null : (Nullable<double>)(dr[i]["VAT_PERCENT"]));
                        infoItem.Name = dr[i]["ITEM_NAME"].ToString();
                        infoUnit.Name = dr[i]["UNIT_NAME"].ToString();
                        info.Descr = dr[i]["ITEM_DESCR"].ToString();
                        info.Qty = (Convert.IsDBNull(dr[i]["ITEM_QTY"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ITEM_QTY"]));
                        info.PriceExVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]));
                        info.PriceInVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_INCLUDE_VAT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["ITEM_PRICE_INCLUDE_VAT"]));
                        info.ExVatAmount = (Convert.IsDBNull(dr[i]["EXCLUDE_VAT_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["EXCLUDE_VAT_AMOUNT"]));
                        info.VatAmount = (Convert.IsDBNull(dr[i]["VAT_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["VAT_AMOUNT"]));
                        info.NetAmount = (Convert.IsDBNull(dr[i]["NET_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["NET_AMOUNT"]));
                        infoPoInstallItem.Qty = (Convert.IsDBNull(dr[i]["PO_ITEM_QTY"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["PO_ITEM_QTY"]));
                        infoPoInstallItem.NetAmount = (Convert.IsDBNull(dr[i]["PO_NET_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["PO_NET_AMOUNT"]));

                        infoItem.InfoUnit = infoUnit;
                        infoPrItem.InfoItem = infoItem;
                        infoBidder.InfoVat = infoVat;
                        infoBidderQuotation.InfoPrItem = infoPrItem;
                        infoBidderQuotation.InfoBidder = infoBidder;
                        infoPoInstallItem.InfoBidderQuotation = infoBidderQuotation;
                        info.InfoPoInstallItem = infoPoInstallItem;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<DeliveryNoteItemInfo> GetPurchaseOrderInstallmentItemForDeliveryNote(int install_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<DeliveryNoteItemInfo> infoList = new List<DeliveryNoteItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@install_seq", install_seq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT pi.ICM_PO_INSTALL_ITEM_SEQ"
                            + ", v.ICM_VAT_SEQ"
                            + ", CAST(CONCAT(v.ICM_VAT_NAME, ' อัตราภาษี ', b.VAT_PERCENT , ' %') AS CHAR) AS ICM_VAT_NAME"
                            + ", b.VAT_PERCENT"
                            + ", COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME) AS ITEM_NAME"
                            + ", pi.ITEM_DESCR"
                            + ", pi.ITEM_QTY"
                            + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS UNIT_NAME"
                            + ", pi.ITEM_PRICE_EXCLUDE_VAT"
                            + ", pi.ITEM_PRICE_INCLUDE_VAT"
                            + ", pi.EXCLUDE_VAT_AMOUNT"
                            + ", pi.VAT_AMOUNT"
                            + ", pi.NET_AMOUNT"
                        + " FROM ICM_PURCHASE_ORDER_INSTALLMENT_ITEM pi"
                            + " INNER JOIN ICM_BIDDER_QUOTATION q ON pi.ICM_BIDDER_QUO_SEQ = q.ICM_BIDDER_QUO_SEQ"
                            + " INNER JOIN ICM_BIDDER b ON q.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ "
                            + " INNER JOIN ICM_PURCHASE_REQUEST_ITEM ri ON q.ICM_PR_ITEM_SEQ = ri.ICM_PR_ITEM_SEQ "
                            + " INNER JOIN BGM_MAS_EXPENSE x ON ri.BGM_EXP_SEQ = x.BGM_EXP_SEQ "
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON ri.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ "
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ "
                            + " INNER JOIN ICM_SYS_VAT v ON b.ICM_VAT_SEQ = v.ICM_VAT_SEQ "
                        + " WHERE pi.ICM_PO_INSTALL_SEQ = @install_seq"
                            + " AND q.SELECTED = @yes_status"
                            + " AND (pi.ITEM_QTY > 0 OR pi.NET_AMOUNT > 0)";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        DeliveryNoteItemInfo info = new DeliveryNoteItemInfo();
                        PurchaseOrderInstallmentItemInfo infoPoInstallItem = new PurchaseOrderInstallmentItemInfo();
                        BidderQuotationInfo infoBidderQuotation = new BidderQuotationInfo();
                        PurchaseRequestItemInfo infoPrItem = new PurchaseRequestItemInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        SysVatInfo infoVat = new SysVatInfo();
                        BidderInfo infoBidder = new BidderInfo();

                        infoPoInstallItem.Seq = Convert.ToInt32(dr[i]["ICM_PO_INSTALL_ITEM_SEQ"]);
                        infoVat.Seq = Convert.ToInt32(dr[i]["ICM_VAT_SEQ"]);
                        infoVat.Name = dr[i]["ICM_VAT_NAME"].ToString();
                        infoBidder.VatPercent = (Convert.IsDBNull(dr[i]["VAT_PERCENT"]) ? null : (Nullable<double>)(dr[i]["VAT_PERCENT"]));
                        infoItem.Name = dr[i]["ITEM_NAME"].ToString();
                        infoUnit.Name = dr[i]["UNIT_NAME"].ToString();
                        info.Descr = dr[i]["ITEM_DESCR"].ToString();
                        info.Qty = (Convert.IsDBNull(dr[i]["ITEM_QTY"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ITEM_QTY"]));
                        info.PriceExVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]));
                        info.PriceInVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_INCLUDE_VAT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["ITEM_PRICE_INCLUDE_VAT"]));
                        info.ExVatAmount = (Convert.IsDBNull(dr[i]["EXCLUDE_VAT_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["EXCLUDE_VAT_AMOUNT"]));
                        info.VatAmount = (Convert.IsDBNull(dr[i]["VAT_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["VAT_AMOUNT"]));
                        info.NetAmount = (Convert.IsDBNull(dr[i]["NET_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["NET_AMOUNT"]));
                        infoPoInstallItem.Qty = (Convert.IsDBNull(dr[i]["ITEM_QTY"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ITEM_QTY"]));
                        infoPoInstallItem.NetAmount = (Convert.IsDBNull(dr[i]["NET_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["NET_AMOUNT"]));

                        infoItem.InfoUnit = infoUnit;
                        infoPrItem.InfoItem = infoItem;
                        infoBidder.InfoVat = infoVat;
                        infoBidderQuotation.InfoPrItem = infoPrItem;
                        infoBidderQuotation.InfoBidder = infoBidder;
                        infoPoInstallItem.InfoBidderQuotation = infoBidderQuotation;
                        info.InfoPoInstallItem = infoPoInstallItem;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
        public IList<DeliveryNoteItemInfo> GetResourceItemDeliveryNoteItem(Nullable<int> Sequence)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<DeliveryNoteItemInfo> infoList = new List<DeliveryNoteItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT dn.ICM_DN_ITEM_SEQ, v.ICM_VAT_SEQ, b.VAT_PERCENT"
                            + ", CAST(CONCAT(v.ICM_VAT_NAME, ' อัตราภาษี ', b.VAT_PERCENT , ' %') AS CHAR) AS ICM_VAT_NAME"
                            + ", COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME) AS ITEM_NAME"
                            + ", ri.ICM_RES_ITEM_SEQ, i.ICM_RES_ITEM_CODE"
                            + ", dn.ITEM_DESCR, dn.ITEM_QTY, pi.ITEM_QTY AS PO_ITEM_QTY"
                            + ", pi.NET_AMOUNT AS PO_NET_AMOUNT, i.ICM_UNIT_SEQ"
                            + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS UNIT_NAME"
                            + ", dn.ITEM_PRICE_EXCLUDE_VAT, dn.ITEM_PRICE_INCLUDE_VAT, dn.EXCLUDE_VAT_AMOUNT"
                            + ", dn.VAT_AMOUNT, dn.NET_AMOUNT"
                        + " FROM ICM_DELIVERY_NOTE_ITEM dn"
                            + " INNER JOIN ICM_PURCHASE_ORDER_INSTALLMENT_ITEM pi ON dn.ICM_PO_INSTALL_ITEM_SEQ = pi.ICM_PO_INSTALL_ITEM_SEQ"
                            + " INNER JOIN ICM_BIDDER_QUOTATION q ON pi.ICM_BIDDER_QUO_SEQ = q.ICM_BIDDER_QUO_SEQ"
                            + " INNER JOIN ICM_BIDDER b ON q.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ "
                            + " INNER JOIN ICM_PURCHASE_REQUEST_ITEM ri ON q.ICM_PR_ITEM_SEQ = ri.ICM_PR_ITEM_SEQ "
                            + " INNER JOIN BGM_MAS_EXPENSE x ON ri.BGM_EXP_SEQ = x.BGM_EXP_SEQ "
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON ri.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ "
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ "
                            + " INNER JOIN ICM_SYS_VAT v ON b.ICM_VAT_SEQ = v.ICM_VAT_SEQ "
                        + " WHERE dn.RECORD_STATUS <> @DeleteRecord"
                        + (Sequence.HasValue ? " AND dn.ICM_DN_ITEM_SEQ = @Sequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        DeliveryNoteItemInfo info = new DeliveryNoteItemInfo();
                        PurchaseOrderInstallmentItemInfo infoPoInstallItem = new PurchaseOrderInstallmentItemInfo();
                        BidderQuotationInfo infoBidderQuotation = new BidderQuotationInfo();
                        PurchaseRequestItemInfo infoPrItem = new PurchaseRequestItemInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        SysVatInfo infoVat = new SysVatInfo();
                        BidderInfo infoBidder = new BidderInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_DN_ITEM_SEQ"]);
                        infoVat.Seq = Convert.ToInt32(dr[i]["ICM_VAT_SEQ"]);
                        infoVat.Name = dr[i]["ICM_VAT_NAME"].ToString();
                        infoBidder.VatPercent = (Convert.IsDBNull(dr[i]["VAT_PERCENT"]) ? null : (Nullable<double>)(dr[i]["VAT_PERCENT"]));
                        infoItem.Seq = (Convert.IsDBNull(dr[i]["ICM_RES_ITEM_SEQ"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ICM_RES_ITEM_SEQ"]));
                        infoItem.Name = dr[i]["ITEM_NAME"].ToString();
                        infoItem.Code = dr[i]["ICM_RES_ITEM_CODE"].ToString();
                        infoUnit.Seq = (Convert.IsDBNull(dr[i]["ICM_UNIT_SEQ"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ICM_UNIT_SEQ"]));
                        infoUnit.Name = dr[i]["UNIT_NAME"].ToString();
                        info.Descr = dr[i]["ITEM_DESCR"].ToString();
                        info.Qty = (Convert.IsDBNull(dr[i]["ITEM_QTY"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ITEM_QTY"]));
                        info.PriceExVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]));
                        info.PriceInVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_INCLUDE_VAT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["ITEM_PRICE_INCLUDE_VAT"]));
                        info.ExVatAmount = (Convert.IsDBNull(dr[i]["EXCLUDE_VAT_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["EXCLUDE_VAT_AMOUNT"]));
                        info.VatAmount = (Convert.IsDBNull(dr[i]["VAT_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["VAT_AMOUNT"]));
                        info.NetAmount = (Convert.IsDBNull(dr[i]["NET_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["NET_AMOUNT"]));
                        infoPoInstallItem.Qty = (Convert.IsDBNull(dr[i]["PO_ITEM_QTY"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["PO_ITEM_QTY"]));
                        infoPoInstallItem.NetAmount = (Convert.IsDBNull(dr[i]["PO_NET_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["PO_NET_AMOUNT"]));

                        infoItem.InfoUnit = infoUnit;
                        infoPrItem.InfoItem = infoItem;
                        infoBidder.InfoVat = infoVat;
                        infoBidderQuotation.InfoPrItem = infoPrItem;
                        infoBidderQuotation.InfoBidder = infoBidder;
                        infoPoInstallItem.InfoBidderQuotation = infoBidderQuotation;
                        info.InfoPoInstallItem = infoPoInstallItem;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
        public void Insert(IList<DeliveryNoteItemInfo> infoList, Nullable<int> dn_seq, DeliveryNoteInfo info, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManagerTrans.CreateParameters(12);

                    dbManagerTrans.AddParameters(0, "@dn_seq", dn_seq);
                    dbManagerTrans.AddParameters(1, "@install_seq", infoList[i].InfoPoInstallItem.Seq);
                    dbManagerTrans.AddParameters(2, "@item_descr", infoList[i].Descr);
                    dbManagerTrans.AddParameters(3, "@item_qty", infoList[i].Qty);
                    dbManagerTrans.AddParameters(4, "@item_price_exclude_vat", infoList[i].PriceExVat);
                    dbManagerTrans.AddParameters(5, "@item_price_include_vat", infoList[i].PriceInVat);
                    dbManagerTrans.AddParameters(6, "@exclude_vat_amount", infoList[i].ExVatAmount);
                    dbManagerTrans.AddParameters(7, "@vat_amount", infoList[i].VatAmount);
                    dbManagerTrans.AddParameters(8, "@net_amount", infoList[i].NetAmount);
                    dbManagerTrans.AddParameters(9, "@item_remark", infoList[i].Remark);
                    dbManagerTrans.AddParameters(10, "@created_by", info.InfoRecord.Created_by);
                    dbManagerTrans.AddParameters(11, "@created_when", info.InfoRecord.Created_when);

                    strSQL = "INSERT INTO ICM_DELIVERY_NOTE_ITEM("
                                + "ICM_DN_SEQ"
                                + ", ICM_PO_INSTALL_ITEM_SEQ"
                                + ", ITEM_DESCR"
                                + ", ITEM_QTY"
                                + ", ITEM_REMARK"
                                + ", ITEM_PRICE_EXCLUDE_VAT"
                                + ", ITEM_PRICE_INCLUDE_VAT"
                                + ", EXCLUDE_VAT_AMOUNT"
                                + ", VAT_AMOUNT"
                                + ", NET_AMOUNT"
                                + ", CREATED_BY"
                                + ", CREATED_WHEN"
                                + ") "
                            + " VALUES (@dn_seq"
                                + ", @install_seq"
                                + ", @item_descr"
                                + ", @item_qty"
                                + ", @item_remark"
                                + ", @item_price_exclude_vat"
                                + ", @item_price_include_vat"
                                + ", @exclude_vat_amount"
                                + ", @vat_amount"
                                + ", @net_amount"
                                + ", @created_by"
                                + ", @created_when"
                                + ")";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                }
            }
            catch
            {
                throw;
            }
        }

        public void Update(IList<DeliveryNoteItemInfo> infoList)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManager.CreateParameters(11);

                    dbManager.AddParameters(0, "@seq", infoList[i].Seq);
                    dbManager.AddParameters(1, "@item_descr", infoList[i].Descr);
                    dbManager.AddParameters(2, "@item_qty", infoList[i].Qty);
                    dbManager.AddParameters(3, "@item_price_exclude_vat", infoList[i].PriceExVat);
                    dbManager.AddParameters(4, "@item_price_include_vat", infoList[i].PriceInVat);
                    dbManager.AddParameters(5, "@exclude_vat_amount", infoList[i].ExVatAmount);
                    dbManager.AddParameters(6, "@vat_amount", infoList[i].VatAmount);
                    dbManager.AddParameters(7, "@net_amount", infoList[i].NetAmount);
                    dbManager.AddParameters(8, "@item_remark", infoList[i].Remark);
                    dbManager.AddParameters(9, "@updated_by", infoList[i].InfoRecord.Updated_by);
                    dbManager.AddParameters(10, "@updated_when", infoList[i].InfoRecord.Updated_when);

                    strSQL = "UPDATE ICM_DELIVERY_NOTE_ITEM"
                        + " SET ITEM_DESCR = @item_descr"
                        + ", ITEM_QTY = @item_qty"
                        + ", ITEM_REMARK = @item_remark"
                        + ", ITEM_PRICE_EXCLUDE_VAT = @item_price_exclude_vat"
                        + ", ITEM_PRICE_INCLUDE_VAT = @item_price_include_vat"
                        + ", EXCLUDE_VAT_AMOUNT = @exclude_vat_amount"
                        + ", VAT_AMOUNT = @vat_amount"
                        + ", NET_AMOUNT = @net_amount"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                        + " WHERE ICM_DN_ITEM_SEQ = @seq";

                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
        }
    }
}
